A database is a collection of stored data that is logically related and that is accessible by one or more users or applications. A popular type of database is the relational database management system (RDBMS), which includes relational tables, also referred to as relations, made up of rows and columns (also referred to as tuples and attributes). Each row represents an occurrence of an entity defined by a table, with an entity being a person, place, thing, or other object about which the table contains information.
One of the goals of a database management system is to optimize the performance of queries for access and manipulation of data stored in the database. Given a target environment, an optimal query plan is selected, with the optimal query plan being the one with the lowest cost, e.g., response time, CPU processing, I/O processing, network processing, etc., as determined by an optimizer. The response time is the amount of time it takes to complete the execution of a query on a given system.
A database index comprises a data structure that improves the speed of operations on a database table. Indexes may be created using one or more columns of a database table, providing the basis for both rapid random look ups and efficient access of ordered records. Some SQL query optimizers will consider dynamically creating an index as part of the processing strategy for an expensive retrieve or join operation where no applicable index is found. In such cases, the index is created prior to the retrieve or join operation being performed, and then dropped immediately after its use in the current request. In practice, the use of such an optimization strategy is rare because the overhead cost of creating the index typically outweighs the resulting cost improvements to the retrieve or join operation.
In the case of a frequently executed query, although the performance of a single instance may not be improved, the cumulative performance over a period of time may be dramatically improved if the index is retained and the overhead of creating the index is amortized over many query instances. Unfortunately, the costing context of all known commercial optimizers is limited to a single SQL request which prevents them from factoring in a query's actual or expected frequency over a period of time. Many vendors do provide index tuning tools that will examine a set of queries (along with their frequencies) and automatically recommend indexes. However, such tools operate only when invoked, are noted for being very resource intensive, and are not active during regular query processing.
Therefore, what is needed is a mechanism that overcomes the described problems and limitations.